﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Collections.Generic;


/// <summary>

/// </summary>
public class UserDAL
{
    SqlConnection connection;
    SqlCommand cmd;

    public UserDAL()
    {
        try
        {
            /*initilaize db variables*/
            connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["loanConnectionString"].ConnectionString);
            cmd = new SqlCommand("", connection);
        }
        catch
        {
            throw new Exception("Unable to connect to database");
        }
    }

    public DataResponse<User> CreateUser(User user)
    {
        DataResponse<User> response = new DataResponse<User>();
 
        try
        {
            if (user!=null)
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "CreateUser";

                if (!string.IsNullOrEmpty(user.Username))
                {
                    cmd.Parameters.AddWithValue("@username", user.Username);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@username", DBNull.Value);
                }

                if (!string.IsNullOrEmpty(user.FirstName))
                {
                    cmd.Parameters.AddWithValue("@first_name", user.FirstName);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@first_name", DBNull.Value);
                }

                if (!string.IsNullOrEmpty(user.LastName))
                {
                    cmd.Parameters.AddWithValue("@last_name", user.LastName);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@last_name", DBNull.Value);
                }

                if (!string.IsNullOrEmpty(user.EmailAddress))
                {
                    cmd.Parameters.AddWithValue("@email_address", user.EmailAddress);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@email_address", DBNull.Value);
                }

                if (!string.IsNullOrEmpty(user.Password))
                {
                    cmd.Parameters.AddWithValue("@password", user.Password);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@password", DBNull.Value);
                }

                if (user.UserRole.RoleID.HasValue)
                {
                    cmd.Parameters.AddWithValue("@role_id", user.UserRole.RoleID);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@role_id", DBNull.Value);
                }

                if (user.CreatedDate.HasValue)
                {
                    cmd.Parameters.AddWithValue("@created_date", user.CreatedDate);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@created_date", DBNull.Value);
                }

                cmd.Connection.Open();

                using(SqlDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        if (dataReader.HasRows)
                        {
                            User userResult = new User();

                            while (dataReader.Read())
                            {
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("error_code")))
                                {
                                    response.ErrorCode = Convert.ToString(dataReader["error_code"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("identity")))
                                {
                                    userResult.UserID = Convert.ToInt64(dataReader["identity"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("error_message")))
                                {
                                    response.ErrorMsg = Convert.ToString(dataReader["error_message"]);
                                }

                                response.Result = userResult;
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (cmd.Connection.State != ConnectionState.Closed)
                cmd.Connection.Close();
        }

        return response;
    }

    public DataResponse<User> GetUser(string username)
    {
        DataResponse<User> response = new DataResponse<User>();

        if (!string.IsNullOrEmpty(username))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetUser";
            cmd.Parameters.AddWithValue("@username", username);

            try
            {
                cmd.Connection.Open();

                using (SqlDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        if (dataReader.HasRows)
                        {
                            User user = new User();
                            while (dataReader.Read())
                            {
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("user_id")))
                                {
                                    user.UserID = Convert.ToInt64(dataReader["user_id"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("username")))
                                {
                                    user.Username = Convert.ToString(dataReader["username"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("first_name")))
                                {
                                    user.FirstName = Convert.ToString(dataReader["first_name"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("last_name")))
                                {
                                    user.LastName = Convert.ToString(dataReader["last_name"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("email_address")))
                                {
                                    user.EmailAddress = Convert.ToString(dataReader["email_address"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("password")))
                                {
                                    user.Password = Convert.ToString(dataReader["password"]);
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("created_date")))
                                {
                                    user.CreatedDate = Convert.ToDateTime(dataReader["created_date"]);
                                }

                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("role_id")))
                                {
                                    user.UserRole = new Role();
                                    user.UserRole.RoleID = Convert.ToInt64(dataReader["role_id"]);

                                    if (!dataReader.IsDBNull(dataReader.GetOrdinal("role_name")))
                                    {
                                        user.UserRole.Name = Convert.ToString(dataReader["role_name"]);
                                    }
                                    if (!dataReader.IsDBNull(dataReader.GetOrdinal("role_description")))
                                    {
                                        user.UserRole.Description = Convert.ToString(dataReader["role_description"]);
                                    }
                                }
                            }
                            response.Result = user;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cmd.Connection.State != ConnectionState.Closed)
                    cmd.Connection.Close();
            }
        }
        return response;
    }

    public DataResponse<bool?> ChangePassword(User user, string newPassword)
    {
        DataResponse<bool?> response = new DataResponse<bool?>();

        try
        {
            if (user != null)
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ChangePassword";

                if (!string.IsNullOrEmpty(user.Username))
                {
                    cmd.Parameters.AddWithValue("@username", user.Username);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@username", DBNull.Value);
                }

                if (!string.IsNullOrEmpty(newPassword))
                {
                    cmd.Parameters.AddWithValue("@new_password", newPassword);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@new_password", DBNull.Value);
                }

                cmd.Connection.Open();

                using (SqlDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("error_code")))
                                {
                                    response.ErrorCode = Convert.ToString(dataReader["error_code"]);
                                }
                                else
                                {
                                    response.Result = true;
                                }
                                if (!dataReader.IsDBNull(dataReader.GetOrdinal("error_message")))
                                {
                                    response.ErrorMsg = Convert.ToString(dataReader["error_message"]);
                                }
                                else
                                {
                                    response.Result = true;
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (cmd.Connection.State != ConnectionState.Closed)
                cmd.Connection.Close();
        }

        return response;
    }

}